//
//  SQLITE.m
//  VVVVVVVSQLIT3
//
//  Created by up72 on 13-7-29.
//  Copyright (c) 2013年 up72. All rights reserved.
//

#define databaseFile @"SSC.sqlite3"

#import "SQLITE.h"

@implementation SQLITE


static SQLITE *sqlite = nil;

+ (id)standardSqlite
{
    if (!sqlite)
    {
        sqlite = [[SQLITE alloc] init];
    }
    
    return sqlite;
}

- (id)init
{
    self = [super init];
    
    if (self)
    {
        [self openSqlite3];
    }
    
    return self;
}

- (BOOL)addSQLObject:(NSMutableDictionary *)anObject forKey:(NSString *)aKey
{
    NSArray *keyArray = [anObject allKeys];
    
    [self createTable:aKey forKey:keyArray];
    return [self insertIntotable:aKey withDictionary:anObject];
}

- (BOOL)removeSQL:(NSString *)table withKey:(NSString *)aKey andValue:(NSString *)value
{
    if (!aKey || !value)
    {
        NSLog(@"缺少key或者value");
        
        return NO;
    }
    NSString *sql = [NSString stringWithFormat:@"delete from %@ where %@ = '%@'",table,aKey,value];
    
    return [self deleteDataWithSQL:sql];
}

- (BOOL)deleteSQLTable:(NSString *)table
{
    if (!table)
    {
        NSLog(@"表名不能为空");

        return NO;
    }
    NSString *sql = [NSString stringWithFormat:@"delete from %@",table];
    
    return [self deleteDataWithSQL:sql];
}

#pragma mark - sql

- (BOOL)openSqlite3
{
    NSArray * paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    
    NSString * path = [[paths objectAtIndex:0] stringByAppendingPathComponent:databaseFile];
    NSLog(@"db path = %@",path);
    int result = sqlite3_open([path UTF8String], &database);
    
    if (result == SQLITE_OK)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}

- (BOOL)createTable:(NSString *)table forKey:(NSArray *)keyArray
{
    if (!keyArray || [keyArray count] < 1)
    {
        return NO;
    }
    
    NSString *keys = [NSString stringWithFormat:@"create table if not exists %@ (id integer primary key autoincrement,",table];
    
    for (NSInteger i = 0; i < [keyArray count]; i ++)
    {
        keys = [keys stringByAppendingFormat:@" %@ text,",[keyArray objectAtIndex:i]];
    }
    
    keys = [keys substringToIndex:keys.length - 1];
    keys = [keys stringByAppendingFormat:@")"];
    char * errorMsg = nil;
    
    int exec_result = sqlite3_exec(database, [keys UTF8String], nil, nil, &errorMsg);
    
    if (sqlite3_exec(database, "COMMIT", NULL, NULL, &errorMsg)==SQLITE_OK) {
        //        NSLog(@”提交事务成功”);
    }
    if (exec_result == SQLITE_OK)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}

- (BOOL)insertIntotable:(NSString *)table withDictionary:(NSMutableDictionary *)dictionary
{
    NSString *aKey     = @"";
    NSString *anObject = @"";
    
    for (NSString *key in [dictionary allKeys])
    {
        aKey = [aKey stringByAppendingFormat:@"%@,",key];
        anObject = [anObject stringByAppendingFormat:@"'%@',",[dictionary objectForKey:key]];
    }
    
    aKey = [aKey substringToIndex:aKey.length - 1];
    anObject = [anObject substringToIndex:anObject.length - 1];
    NSString *sql = [NSString stringWithFormat:@"insert into %@(%@) values(%@)",table,aKey,anObject];
    char * errorMsg;
    int result = sqlite3_exec(database, [sql UTF8String], nil, nil, &errorMsg);
    
    if (result == SQLITE_OK)
    {
        return YES;
    }
    else
    {
        NSLog(@"errorMsg == %s",errorMsg);
        return NO;
    }
}


- (NSMutableArray *)selectSQLFromtable:(NSString *)table
                            elementKey:(NSString *)keyWorld
                          elementValue:(NSString *)value
                                 order:(NSString *)orderKey
                                 limit:(NSInteger)limit
                                offset:(NSUInteger)offset
                                 order:(OEDER)order
{
    NSString *sql = [NSString stringWithFormat:@"select * from %@",table];
    
    if (limit > 0)
    {
        if (keyWorld && keyWorld.length > 1)
        {
            NSString *orderString = @"asc";
            
            if (order == DESC)
            {
                orderString = @"desc";
            }
            
            sql = [NSString stringWithFormat:@"select * from %@ order by %@ %@ limit %ld offset %lu",table,keyWorld,orderString,(long)limit,(unsigned long)offset];
            
            if (value && value.length > 0)
            {
                sql = [NSString stringWithFormat:@"select * from %@ where %@='%@' order by %@ %@ limit %ld offset %lu",table,keyWorld,value,orderKey,orderString,(long)limit,(unsigned long)offset];
            }
        }
    }
    else
    {
        if (keyWorld.length > 0)
        {
            NSString *orderString = @"asc";
            
            if (order == DESC)
            {
                orderString = @"desc";
            }
            
            if (value.length > 0)
            {
                sql = [NSString stringWithFormat:@"select * from %@ where %@='%@' order by %@ %@",table,keyWorld,value,@"id",orderString];
            }
            else
            {
                sql = [NSString stringWithFormat:@"select * from %@ order by %@ %@",table,keyWorld,orderString];
            }
        }
    }
    
    sqlite3_stmt * stmt;
    int result = sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil);
    
    NSMutableArray *array = [NSMutableArray array];
    
    if (result == SQLITE_OK)
    {
        NSInteger row = 0;
        
        NSMutableArray *colKeyArray = [self colKeyFromtable:table];
        NSInteger colCount = [[self colKeyFromtable:table] count];
        
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            NSMutableDictionary *dict = [NSMutableDictionary dictionary];
            
            int col = 0;
            char *colData = (char *)sqlite3_column_text(stmt, col);
            
            while (col < colCount)
            {
                NSString *fieldValue=[[NSString alloc] initWithCString:(char *)sqlite3_column_text(stmt, col)
                                                              encoding:NSUTF8StringEncoding];
                
                [dict setObject:fieldValue forKey:[colKeyArray objectAtIndex:col]];
                
                
                col ++;
                colData = (char *)sqlite3_column_text(stmt, col);
                
                if (!colData)
                {
                    colData = "";
                }
            }
            
            [array addObject:dict];
            
            row ++;
        }
        
        return array;
    }
    else
    {
        return array;
    }
    
}

- (NSMutableArray *)selectSQLFromtable:(NSString *)table
                            elementKey:(NSString *)keyWorld
                          elementValue:(NSString *)value
                                 limit:(NSInteger)limit
                                offset:(NSUInteger)offset
                                 order:(OEDER)order
{
    NSString *sql = [NSString stringWithFormat:@"select * from %@",table];
    
    if (limit > 0)
    {
        if (keyWorld && keyWorld.length > 1)
        {
            NSString *orderString = @"asc";
            
            if (order == DESC)
            {
                orderString = @"desc";
            }
            
            sql = [NSString stringWithFormat:@"select * from %@ order by %@ %@ limit %ld offset %lu",table,keyWorld,orderString,(long)limit,(unsigned long)offset];
            
            if (value && value.length > 0)
            {
                sql = [NSString stringWithFormat:@"select * from %@ where %@='%@' order by %@ %@ limit %ld offset %lu",table,keyWorld,value,keyWorld,orderString,(long)limit,(unsigned long)offset];
            }
        }
    }
    else
    {
        if (keyWorld.length > 0)
        {
            NSString *orderString = @"asc";
            
            if (order == DESC)
            {
                orderString = @"desc";
            }
            
            if (value.length > 0)
            {
                sql = [NSString stringWithFormat:@"select * from %@ where %@='%@' order by %@ %@",table,keyWorld,value,@"id",orderString];
            }
            else
            {
                sql = [NSString stringWithFormat:@"select * from %@ order by %@ %@",table,keyWorld,orderString];
            }
        }
    }
    return [self selectDataWithSQL:sql table:table];
    
    
}

- (NSMutableArray *)selectDistinctWithKey:(NSString *)key table:(NSString *)table
{
    NSString *sql = [NSString stringWithFormat:@"SELECT distinct %@ FROM %@",key,table];
    sqlite3_stmt * stmt;
    int result = sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil);
    
    NSMutableArray *array = [NSMutableArray array];
    
    if (result == SQLITE_OK)
    {
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            int col = 0;

            NSString *fieldValue=[[NSString alloc] initWithCString:(char *)sqlite3_column_text(stmt, col)
                                                          encoding:NSUTF8StringEncoding];
            [array addObject:fieldValue];
        }
        
        return array;
    }
    else
    {
        return array;
    }
}

- (BOOL)updateSQL:(NSString *)table WithKey:(NSString *)key fromValue:(NSString *)fromValue toValue:(NSString *)toValue
{
    NSString *sql = [NSString stringWithFormat:@"update %@ set %@='%@' where %@='%@'",table,key,toValue,key,fromValue];
    return [self updateDataWithSQL:sql];
}

- (BOOL)updateSQL:(NSString *)table WithKey:(NSString *)key andValue:(NSString *)value dict:(NSMutableDictionary *)dict
{
    NSString *keyValue = @"";
    
    for (NSString *string in dict.allKeys)
    {
        keyValue = [keyValue stringByAppendingFormat:@"%@='%@',",string,[dict objectForKey:string]];
    }
    
    keyValue = [keyValue substringToIndex:keyValue.length - 1];
    
    NSString *sql = [NSString stringWithFormat:@"update %@ set %@ where %@='%@'",table,keyValue,key,value];
    
    return [self updateDataWithSQL:sql];
}

#pragma mark 列名

- (NSMutableArray *)colKeyFromtable:(NSString *)table
{
    NSMutableArray *colKeys = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"PRAGMA table_info(%@)",table];
    sqlite3_stmt *stmt;
    
    if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, NULL) != SQLITE_OK)
    {
        return colKeys;
    }
    
    while(sqlite3_step(stmt) == SQLITE_ROW)
    {
        NSString *fieldName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 1)];
        [colKeys addObject:fieldName];
    }
    
    return colKeys;
}

//#pragma mark表的行数

- (int)counttable:(NSString *)table key:(NSString *)key value:(NSString *)value
{
    
    
    NSString *sql = [NSString stringWithFormat:@"select count(*) from %@ where %@='%@'",table,key,value];
    
    if (key.length > 0 && value.length > 0) {
        sql = [NSString stringWithFormat:@"select count(*) from %@ where %@='%@'",table,key,value];
    } else {
        sql = [NSString stringWithFormat:@"select count(*) from %@",table];
    }
    //模糊查询
//    NSString *sql = [NSString stringWithFormat:@"select count(*) from %@ where %@ like '%%%@%%'",table,key,value];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil);
    
    if (result == SQLITE_OK)
    {
        int count = 0;
        if (sqlite3_step(stmt))
        {
            count = sqlite3_column_int(stmt, 0);
        }
        sqlite3_finalize(stmt);
        return count;
    }
    else
    {
        sqlite3_finalize(stmt);
        return 0;
    }
}


- (int)counttable:(NSString *)table
{
    NSString *sql = [NSString stringWithFormat:@"select count(id) from %@",table];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil);
    
    if (result == SQLITE_OK)
    {
        int count = 0;
        if (sqlite3_step(stmt))
        {
            count = sqlite3_column_int(stmt, 0);
        }
        sqlite3_finalize(stmt);
        return count;
    }
    else
    {
        sqlite3_finalize(stmt);
        return 0;
    }
}

- (NSString *)sumWithKey:(NSString *)key form:(NSString *)table whereKey:(NSString *)whereKey whereValue:(NSString *)whereValue
{
    NSString *sql = [NSString stringWithFormat:@"select sum(%@) as OrderTotal from %@",key,table];
    
    if (whereKey && whereKey)
    {
        sql = [NSString stringWithFormat:@"select sum(%@) as OrderTotal from %@ where %@='%@'",key,table,whereKey,whereValue];
    }
    
    sqlite3_stmt * stmt;
    
    if(sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil) ==SQLITE_OK)
    {
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            int sum = sqlite3_column_int(stmt, 0);
            
            return [NSString stringWithFormat:@"%d",sum];
        }
    }
    
    return @"0";
}


#pragma mark - SQL 语句



- (BOOL)insertDataWithSQL:(NSString *)sql
{
    char * errorMsg;
    int result = sqlite3_exec(database, [sql UTF8String], nil, nil, &errorMsg);
    
    if (result == SQLITE_OK)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}


- (NSMutableArray *)selectDataWithSQL:(NSString *)sql table:(NSString *)table
{
    sqlite3_stmt * stmt;
    int result = sqlite3_prepare_v2(database, [sql UTF8String], -1, &stmt, nil);
    
    NSMutableArray *array = [NSMutableArray array];
    
    if (result == SQLITE_OK)
    {
        NSInteger row = 0;
        
        NSMutableArray *colKeyArray = [self colKeyFromtable:table];
        NSInteger colCount = [[self colKeyFromtable:table] count];
        
        while (sqlite3_step(stmt) == SQLITE_ROW)
        {
            NSMutableDictionary *dict = [NSMutableDictionary dictionary];
            
            int col = 0;
            char *colData = (char *)sqlite3_column_text(stmt, col);
            
            while (col < colCount)
            {
                NSString *fieldValue=[[NSString alloc] initWithCString:(char *)sqlite3_column_text(stmt, col)
                                                              encoding:NSUTF8StringEncoding];
                
                [dict setObject:fieldValue forKey:[colKeyArray objectAtIndex:col]];
                
                col ++;
                colData = (char *)sqlite3_column_text(stmt, col);
                
                if (!colData)
                {
                    colData = "";
                }
            }
            
            [array addObject:dict];
            
            row ++;
        }
        
        return array;
    }
    else
    {
        return array;
    }
}

- (BOOL)updateDataWithSQL:(NSString *)sql
{
    char * errorMsg;
    int result = sqlite3_exec(database, [sql UTF8String], nil, nil, &errorMsg);
    
    if (result == SQLITE_OK)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}

- (BOOL)deleteDataWithSQL:(NSString *)sql
{
    char * errorMsg;
    int result = sqlite3_exec(database, [sql UTF8String], nil, nil, &errorMsg);
    
    if (result == SQLITE_OK)
    {
        return YES;
    }
    else
    {
        return NO;
    }
}



@end
